package com.xcx.service.user;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.xcx.context.BaseContext;
import com.xcx.utils.SQLUtils;
import com.xcx.utils.TextUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestTemplate;

import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

@Slf4j
@Service
public class KnowledgeBaseService {

    @Autowired
    private RestTemplate restTemplate;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private TextUtils textUtils;

    @Autowired
    private SQLUtils sqlUtils;

    public String conversation() {
        String conversationId = (String) redisTemplate.opsForValue().get(BaseContext.getCurrentId() + "converId");
         if (conversationId == null) {
            String url = "https://qianfan.baidubce.com/v2/app/conversation";
            HttpHeaders headers = new HttpHeaders();
            headers.set("X-Appbuilder-Authorization", "Bearer bce-v3/ALTAK-k0SITh6l8LsdFpZebKpBW/e5215549a886e7aa1c7961f1f2116cfcdbb6f2dd");
            JSONObject body = new JSONObject();
            body.put("app_id", "859e6d60-3950-4c9f-93f2-042d864ca8c7");
            String conversation_id = restTemplate.postForObject(url, new HttpEntity<>(body, headers), String.class);
            redisTemplate.opsForValue().set(BaseContext.getCurrentId() + "converId", conversation_id, 3, TimeUnit.DAYS);
            return conversation_id;
        } else {
            return conversationId;
        }
    }

    public JSONObject chat(Map<String, String> map){
        log.info("chat{}***{}",map.get("conversationId"),map.get("query"));
        String url = "https://qianfan.baidubce.com/v2/app/conversation/runs";
        HttpHeaders headers = new HttpHeaders();
        headers.set("Content-Type","application/json");
        headers.set("Authorization","Bearer bce-v3/ALTAK-k0SITh6l8LsdFpZebKpBW/e5215549a886e7aa1c7961f1f2116cfcdbb6f2dd");

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("app_id", "859e6d60-3950-4c9f-93f2-042d864ca8c7");
        jsonObject.put("query", map.get("query"));
        jsonObject.put("stream", false);
        jsonObject.put("conversation_id", map.get("conversationId"));

        String s = restTemplate.postForObject(url, new HttpEntity<>(jsonObject, headers), String.class);
        log.info("chat返回{}",s);
        JSONObject jsonObject1 = JSON.parseObject(s);
        return jsonObject1;
    }


    public List<Map<String, Object>> getQuestions(Map<String, String> map) {
        String keyword = map.get("keyword");
        List<String> signaleWords = textUtils.getSignaleWord(keyword);
        if (signaleWords.size() <= 1){
            List<Map<String, Object>> maps = jdbcTemplate.queryForList("select question from a_mwf_knowledge where keyword = '" + keyword+"'");
            return maps;
        }else {
            return getQuestionsByTwoKeyWords(signaleWords.get(0),signaleWords.get(1));
        }
    }

    public List<Map<String, Object>> getQuestionsByTwoKeyWords(String key1,String key2) {
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT DISTINCT question FROM a_mwf_knowledge WHERE question IN (SELECT question FROM a_mwf_knowledge WHERE keyword = '"+key1+"') " +
                "AND question IN (SELECT question FROM a_mwf_knowledge WHERE keyword = '"+key2 +"')");
        return maps;
    }


    public JSONObject getDataAndTime(Map<String, String> map) {
        short storeLevel = Short.parseShort(map.get("storeLevel"));
        switch (storeLevel){
            case 1:
                String sql = "SELECT e.xse 销售额,COALESCE(d.rs, 0) AS 绑定人数 FROM( SELECT CODE, NAME FROM rm_da_qw_employees WHERE CODE = '"+map.get("userId")+"' ) a LEFT JOIN (SELECT femp_code,sum( ftax_amount ) xse FROM rm_da_sales_dtl WHERE fbilldate = '2024-6-11' AND femp_code ='"+map.get("userId")+"'GROUP BY femp_code ) e ON a.CODE = e.femp_code LEFT JOIN (SELECT a.fclerk_code,count( a.fmember_card_no ) rs FROM rm_da_qvx_vip_bind a WHERE DATE ( a.fbind_time )= DATE (now()) AND a.fclerk_code = '"+map.get("userId")+"' AND a.fclerk_code <> ' ' AND concat( a.fuserid, a.fclerk_code ) NOT IN ( SELECT concat( fuserid, fclerk_code ) FROM rm_da_qvx_vx_friend_lg WHERE DATE ( fcreatetime )= DATE ( now()) AND ffollow_add_way = '202' ) GROUP BY a.fclerk_code ) d ON a.CODE = d.fclerk_code";
                List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
                return JSON.parseObject(JSON.toJSONString(list.get(0)));
            case 2:
                String sql1 = " select nvl(a.jshj,0) 销售额,case when nvl(xsrw.rwje,0)<>0 then round(a.jshj*100/xsrw.rwje,2) else 0 end ||'%' 销售进度  from (select sum(jshj) jshj from subfhd where  kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' and nvl(bm,' ')<>'积分兑换' and subbh='"+map.get("departCode")+"' ) a left join (select * from vw_zkzygxrw  where subbh='"+map.get("departCode")+"' and pzlb='绩效销售额任务') xsrw on 1=1";
                String query1 = sqlUtils.sendSql(sql1, "query");
                return JSON.parseArray(query1).getJSONObject(0);
            case 5:
            case 7:
                String sql0 = "select C_MDFQ1 from c_wjqdatauser where gh = '"+map.get("userId")+"'";
                String query0 = sqlUtils.sendSql(sql0, "query");
                String c_mdfq1 = JSON.parseArray(query0).getJSONObject(0).getString("C_MDFQ1");
                String sql5 = "select round(sum(c.jshj)/10000,2) jshj,round(sum(c.ml)/10000,2) ml,case when nvl(sum(b.jxrw),0)<>0 then round(sum(c.jshj)*100/sum(b.jxrw),2) else 0 end xswcl,case when nvl(sum(b.mlrw),0)<>0 then round(sum(nvl(c.ml,0)+nvl(d.ml,0))*100/sum(b.mlrw),2) else 0 end mlwcl from gl_custom a left join organization f on substr(a.tjbh,1,4)=f.code left join (select a.subbh,a.yxse*(b.xsxs/100) jxrw,a.ymle*(d.xsxs/100) mlrw from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='绩效销售额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1 left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join  C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='月毛利额任务' and trunc(b.ksrq)=trunc(sysdate)) d on 1=1) b on a.tjbh=b.subbh left join (select case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end subbh,sum(jshj) jshj,sum(ml) ml from subfhd where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' group by case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end) c on a.tjbh=c.subbh left join (select case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end subbh,abs(sum(ml)) ml from subfhd a left join c_zkmdbmlpzmx c on a.hedgehh=c.hh where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' and a.ml<0 and a.sl>0  and c.hh is not null AND substr(a.lsh,1,8)=a.subbh group by case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end) d on a.tjbh=d.subbh where (a.c_mdfq1= '"+c_mdfq1+"' or f.name = '"+c_mdfq1+"')";
                String query5 = sqlUtils.sendSql(sql5, "query");
                return JSON.parseArray(query5).getJSONObject(0);
            case 9:
                String sql9 = "select round(sum(c.jshj)/10000,1) jshj,round(sum(c.ml)/10000,1) ml,case when nvl(sum(b.jxrw),0)<>0 then round(sum(c.jshj)*100/sum(b.jxrw),2) else 0 end xswcl, case when nvl(sum(b.mlrw),0)<>0 then round(sum(nvl(c.ml,0)+nvl(d.ml,0))*100/sum(b.mlrw),2) else 0 end mlwcl from gl_custom a left join (select a.subbh,a.yxse*(b.xsxs/100) jxrw,a.ymle*(d.xsxs/100) mlrw from (select * from c_mdjyys where nd=to_char(sysdate,'yyyy') and yf=to_char(sysdate,'mm')) a left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='绩效销售额任务' and trunc(b.ksrq)=trunc(sysdate)) b on 1=1 left join (select a.rwlb,b.xsxs from C_ZKMTXSSCB a left join C_ZKMTXSSCBDETAIL b on a.guid=b.mainguid where a.rwlb='月毛利额任务' and trunc(b.ksrq)=trunc(sysdate)) d on 1=1) b on a.tjbh=b.subbh left join (select case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end subbh,sum(jshj) jshj,sum(ml) ml from subfhd where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ')<>'￥' group by case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end) c on a.tjbh=c.subbh left join (select case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end subbh,abs(sum(ml)) ml from subfhd a left join c_zkmdbmlpzmx c on a.hedgehh=c.hh where kdrq=trunc(sysdate) and nvl(bz,' ')<>'会员礼品成本调整单' and nvl(bz,' ') <>'￥' and a.ml<0 and a.sl>0 and c.hh is not null AND substr(a.lsh,1,8)=a.subbh group by case when bz='互联网医院膏方销售' then CUSTOMERINFO else subbh end) d on a.tjbh=d.subbh where a.c_mdfq in (select c_mdfq from c_wjqdatauser where gh="+map.get("userId")+")";
                String query9 = sqlUtils.sendSql(sql9, "query");
                return JSON.parseArray(query9).getJSONObject(0);
            default:
                log.info("异常级别：{}",storeLevel);
                return null;
        }
    }
}
